MySQL Typos Can Be Scary

Mr. Muskrat on 2005-07-12T16:30:55

A coworker just showed me a MySQL query that gave him a pretty good scare yesterday.

mysql> SELECT COUNT(*) MISSION_CRITICAL_TABLE;
+------------------------+
| MISSION_CRITICAL_TABLE |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

I did a double take when he showed it to me because I too missed the typo at first. That is a valid MySQL statement that means show me the count of nothing and call it MISSION_CRITICAL_TABLE. You see the 'AS' is optional. What he intended to type was:

mysql> SELECT COUNT(*) FROM MISSION_CRITICAL_TABLE;
+----------+
| COUNT(*) |
+----------+
|  7030295 |
+----------+
1 row in set (0.00 sec)

Okay, you can stop laughing at us now. I just thought that this was something that everyone who uses MySQL should be aware of.

---

My first big $job screw-up was an SQL typo/thinko

dug on 2005-07-12T17:34:33

Some ten years ago I an 'UPDATE PRODUCTION_TABLE SET FOO = "BAR"' query to "correct" a mistake that someone else had made. Notice the lack of the WHERE clause ;-) Needless to say I haven't run an UPDATE without first running its SELECT counterpart since.

-- Douglas Hunter

Wow

jdavidb on 2005-07-12T18:18:06

I will never complain about having to add "FROM DUAL" in Oracle again!